/*
  # 创建机器人资料表

  1. 新表
    - `bot_profiles`
      - `id` (uuid, 主键)
      - `bot_id` (uuid, 外键)
      - `username` (text)
      - `nickname` (text)
      - `bind_uin` (bigint)
      - `bind_email` (text)
      - `bind_mobile` (text)
      - `sex` (integer)
      - `level` (integer)
      - `experience` (integer)
      - `alias` (text)
      - `big_head_img_url` (text)
      - `small_head_img_url` (text)
      - `updated_at` (timestamp)

  2. 安全
    - 启用RLS
    - 添加针对已认证用户的策略
*/

-- 首先检查表是否已存在，如果存在则不重新创建
DO $$
BEGIN
  IF NOT EXISTS (SELECT FROM pg_tables WHERE schemaname = 'public' AND tablename = 'bot_profiles') THEN
    -- 创建表
    CREATE TABLE bot_profiles (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      bot_id uuid NOT NULL REFERENCES bots(id) ON DELETE CASCADE,
      username text,
      nickname text,
      bind_uin bigint,
      bind_email text,
      bind_mobile text,
      sex integer,
      level integer,
      experience integer,
      alias text,
      big_head_img_url text,
      small_head_img_url text,
      updated_at timestamptz DEFAULT now(),
      CONSTRAINT bot_profiles_bot_id_key UNIQUE (bot_id)
    );
    
    -- 创建索引
    CREATE INDEX idx_bot_profiles_bot_id ON bot_profiles(bot_id);
    CREATE INDEX idx_bot_profiles_updated_at ON bot_profiles(updated_at DESC);
    
    -- 启用RLS
    ALTER TABLE bot_profiles ENABLE ROW LEVEL SECURITY;
  END IF;
END
$$;

-- 删除可能存在的策略
DROP POLICY IF EXISTS "Users can view profiles for their bots" ON bot_profiles;
DROP POLICY IF EXISTS "Users can insert profiles for their bots" ON bot_profiles;
DROP POLICY IF EXISTS "Users can update profiles for their bots" ON bot_profiles;

-- 创建策略
CREATE POLICY "Users can view profiles for their bots"
  ON bot_profiles
  FOR SELECT
  TO authenticated
  USING (EXISTS (
    SELECT 1 FROM bots
    WHERE bots.id = bot_profiles.bot_id
    AND bots.user_id = auth.uid()
  ));

CREATE POLICY "Users can insert profiles for their bots"
  ON bot_profiles
  FOR INSERT
  TO authenticated
  WITH CHECK (EXISTS (
    SELECT 1 FROM bots
    WHERE bots.id = bot_profiles.bot_id
    AND bots.user_id = auth.uid()
  ));

CREATE POLICY "Users can update profiles for their bots"
  ON bot_profiles
  FOR UPDATE
  TO authenticated
  USING (EXISTS (
    SELECT 1 FROM bots
    WHERE bots.id = bot_profiles.bot_id
    AND bots.user_id = auth.uid()
  ))
  WITH CHECK (EXISTS (
    SELECT 1 FROM bots
    WHERE bots.id = bot_profiles.bot_id
    AND bots.user_id = auth.uid()
  ));

-- 创建更新触发器
DROP TRIGGER IF EXISTS update_bot_profiles_updated_at ON bot_profiles;
DROP FUNCTION IF EXISTS update_bot_profiles_updated_at();

CREATE OR REPLACE FUNCTION update_bot_profiles_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_bot_profiles_updated_at
  BEFORE UPDATE ON bot_profiles
  FOR EACH ROW
  EXECUTE FUNCTION update_bot_profiles_updated_at(); 